﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using NB.MGJ.Model;
using System.Collections;
using MySql.Data.MySqlClient;
using NB.MGJ.FrameWork;
using NB.MGJ.Common;
using System.Linq.Expressions;

namespace NB.MGJ.FrameWork
{
    public class BaseRepository
    {
       public BaseExecute _base ;

        public BaseRepository()
        {
            _base  = new BaseExecute();
        }


        #region SQL&StoredProcedure
        /// <summary>
        /// 执行SQL语句
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="parameter"></param>
        /// <returns></returns>
        public T ExecuteText<T>(string sql, MySqlParameter[] parameter = null)
        {

            object result = null;
    
            _base.CommandAction(sql, parameter, false, reader =>
            {
                while (reader.Read())
                {
                    result = _base.ReaderObject<T>(reader);
                }
            });

            return result == null ? default(T) : (T)result;
        }
        /// <summary>
        /// 执行SQL语句   返回是否为空
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="parameter"></param>
        /// <returns></returns>
        public bool ExecuteTextScater<T>(string sql, MySqlParameter[] parameter = null)
        {

            object result = _base.CommandActionScater(sql, parameter);


            if (result == null)
            {
                return false;
            }
            else
            {
                return true;
            }
            //_base.CommandActionScater(sql, parameter, false, reader =>
            //{
            //    while (reader.Read())
            //    {
            //        result = _base.ReaderObject<T>(reader);
            //    }
            //});

            //return result == null ? default(T) : (T)result;
        }

        /// <summary>
        /// 执行sql语句
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public T ExecuteSql<T>(string sql, MySqlParameter[] parameter = null)
        {
            object result = null;
            _base.CommandExecuteNonQuery(sql, parameter, count =>
            {
                result = count;
            });
            //var g= (T)result;
            //var d = default(T);
            return result == null ? default(T) : (T)result;
        }

      
        /// <summary>
        /// 执行sql语句、返回插入行的ID
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public T ExecuteSqlReturnID<T>(string sql, MySqlParameter[] parameter = null)
        {
            object result = null;
            _base.CommandExecuteNonQueryReturnID(sql, parameter, count =>
            {
                result = count;
            });

            return result == null ? default(T) : (T)result;
        }
        /// <summary>
        /// 执行无毁
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="procedurename"></param>
        /// <param name="parameter"></param>
        /// <param name="output"></param>
        /// <returns></returns>
        public T ExecuteProcedure<T>(string procedurename, MySqlParameter[] parameter = null, bool output = false)
        {
            object result = null;
            _base.CommandExecuteProcedure(procedurename, parameter, output, r =>
            {
                result = r;
            });
            return result == null ? default(T) : (T)result;
        }


        /// <summary>
        /// 执行SQL语句,返回集合数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="parameter"></param>
        /// <returns></returns>
        public IEnumerable<T> ExecuteSqlCollection<T>(string sql, MySqlParameter[] parameter = null)
        {
            var _list = new List<T>();
            _base.CommandExecuteReader(sql, parameter, reader =>
            {
                object result = null;
                while (reader.Read())
                {
                    result = _base.ReaderObject<T>(reader);
                    if (result == null)
                    {
                        _list.Add(default(T));
                    }
                    else
                    {
                        _list.Add((T)result);
                    }
                }
            });
            return _list;
        }

        /// <summary>
        /// 执行SQL语句,返回集合数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="parameter"></param>
        /// <returns></returns>
        public IEnumerable<T> ExecuteTextCollection<T>(string sql, MySqlParameter[] parameter = null)
        {
            var returnList = new List<T>();

            _base.CommandAction(sql, parameter, false, reader =>
            {
                object result = null;
                while (reader.Read())
                {
                    result = _base.ReaderObject<T>(reader);
                    if (result == null)
                    {
                        returnList.Add(default(T));
                    }
                    else
                    {
                        returnList.Add((T)result);
                    }
                }
            });

            return returnList;
        }


        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="procedureName"></param>
        /// <param name="parameter"></param>
        /// <returns></returns>
        public T ExecuteStoredProcedure<T>(string procedureName, MySqlParameter[] parameter = null)
        {

            object result = null;

            _base.CommandAction(procedureName, parameter, true, reader =>
            {
                while (reader.Read())
                {
                    result = _base.ReaderObject<T>(reader);
                }
            });

            return result == null ? default(T) : (T)result;
        }

        /// <summary>
        /// 执行存储过程,返回集合数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="procedureName"></param>
        /// <param name="parameter"></param>
        /// <returns></returns>
        public IEnumerable<T> ExecuteStoredProcedureCollection<T>(string procedureName, MySqlParameter[] parameter = null)
        {
            var returnList = new List<T>();

            _base.CommandAction(procedureName, parameter, true, reader =>
            {
                object result = null;
                while (reader.Read())
                {
                    result = _base.ReaderObject<T>(reader);
                    if (result == null)
                    {
                        returnList.Add(default(T));
                    }
                    else
                    {
                        returnList.Add((T)result);
                    }
                }
            });

            return returnList;
        }

        /// <summary>
        ///存储过程Common_PageList 参数转换SqlParameter
        /// </summary>
        /// <param name="pager">PagerModel实体</param>
        /// <returns>SqlParameter[]</returns>
        public MySqlParameter[] SqParameterRefactor(ref PagerModel pager)
        {
             //CALL Common_PageList(
             //              ' ',
             //              ' ',
             //              10,1
             //              ,' ',
             //              '',@abc);  
             //              SELECT @abc;
            MySqlParameter[] parameters = new MySqlParameter[] 
                {
                    new MySqlParameter("@p_table_name", pager.TblName),//表名
                    new MySqlParameter("@p_order_string", pager.FldSort),//排序
                    new MySqlParameter("@p_page_size", pager.PageSize),//页数
                    new MySqlParameter("@p_page_now", pager.Page),//起始页
                    new MySqlParameter("@p_where_string", pager.StrCondition),//条件
                    new MySqlParameter("@p_fields", pager.FldName),//字段

                    AddOutParameter("@p_out_rows", DbType.Int32, 10)//返回总行数
                };
            return parameters;
        }

        /// <summary>
        /// SqlParameter[]  声明输出参数
        /// </summary>
        /// <param name="parameterName">参数名</param>
        /// <param name="dbType">参数类型</param>
        /// <param name="size">大小</param>
        /// <returns>SqlParameter</returns>
        public static MySqlParameter AddOutParameter(string parameterName, DbType dbType, int size)
        {
            MySqlParameter sp = new MySqlParameter();
            sp.DbType = dbType;
            sp.ParameterName = parameterName;
            sp.Size = size;
            sp.Direction = ParameterDirection.Output;
            return sp;
        }
        #endregion

        #region 新增修改
        /// <summary>
        /// 通过泛型插入数据返回受影响的行数
        /// </summary>
        /// <typeparam name="T">类名称</typeparam>
        /// <param name="obj">类对象,如果要插入空值，请使用@NULL</param>
        /// <returns>受影响行数</returns>    
        public int Insert<T>(T obj)
        {
            try
            {
                string strSQL = _base.GetInsertSQL(obj).ToString();
                int result = ExecuteSql<int>(strSQL);//SQLPlus.ExecuteScalar(CommandType.Text, strSQL, null);
                return Convert.IsDBNull(result) ? 0 : Convert.ToInt32(result);
            }
            catch (Exception )
            {
                return 0;
            }
        }

        /// <summary>
        /// 通过泛型插入数据返回受影响的行数
        /// </summary>
        /// <typeparam name="T">类名称</typeparam>
        /// <param name="obj">类对象,如果要插入空值，请使用@NULL</param>
        /// <returns>插入的ID</returns>
        public int InsertReturnID<T>(T obj)
        {
            string strSQL = _base.GetInsertSQL(obj).ToString();
            int result = ExecuteSqlReturnID<int>(strSQL);//SQLPlus.ExecuteScalar(CommandType.Text, strSQL, null);
            return Convert.IsDBNull(result) ? 0 : Convert.ToInt32(result);
        }

    
        /// <summary>
        /// 通过泛型更新数据
        /// </summary>
        /// <typeparam name="T">类名称</typeparam>
        /// <param name="obj">类对象,如果要更新空值，请使用@NULL</param>
        /// <returns>更新结果,大于0为更新成功</returns>
        public int Update<T>(T obj)
        {
            try
            {
                string strSQL = _base.GetUpdateSQL(obj).ToString();
                int result = ExecuteSql<int>(strSQL); 
                return Convert.IsDBNull(result) ? 0 : result;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 重载更新
        /// 0.1版
        /// Lv
        /// 暂不支持多条更新
        /// </summary>
        public int Update<T>(Expression<Func<T, bool>> expression, Action<T> action) where T : class,new()
        {
            if (expression == null || action == null)
            {
                throw new ArgumentNullException("null");
            }
            try
            {
                T _t = new T();
                string _connectionString = ConfigurationManager.ConnectionStrings["mgj_basemysql"].ConnectionString;
                if (string.IsNullOrWhiteSpace(_connectionString)) { return 0; }
                using (MySqlConnection conn = new MySqlConnection(_connectionString))
                {
                    using (MySqlCommand cmd = conn.CreateCommand())
                    {
                        conn.Open();
                        string _tableName = GetTableName(_t);
                        if (string.IsNullOrWhiteSpace(_tableName)) { return 0; }
                        string _where = GetWhereString(expression);
                        if (string.IsNullOrWhiteSpace(_where)) { return 0; }
                        cmd.CommandType = CommandType.Text;
                        cmd.CommandText = string.Format("SELECT * FROM {0} WHERE {1}", _tableName, _where);
                        MySqlDataReader reader = cmd.ExecuteReader();
                        while (reader.Read())
                        {
                            _t = _base.ReaderObject<T>(reader) as T;
                        }
                        if (_t == null) { return 0; }
                        action(_t);
                        reader.Close();
                        cmd.CommandText = GetUpdateSqlString<T>(_t).ToString();
                        return cmd.ExecuteNonQuery();
                    }
                }
            }
            catch
            {
                return 0;
            }
        }

        /// <summary>
        /// 修改后获取更新SQL方法
        /// </summary>
        private StringBuilder GetUpdateSqlString<T>(T obj)
        {
            Type t = obj.GetType();//获得该类的Type
            string tableKey = "";
            string keyValue = "";
            string tableName = t.Name;
            StringBuilder strSQL = new StringBuilder();
            strSQL.Append("update " + tableName + " set ");
            List<string> _SubSQL = new List<string>();
            foreach (System.Reflection.PropertyInfo pi in t.GetProperties())
            {
                object[] objArray = pi.GetCustomAttributes(false);
                object name = pi.Name;//用pi.GetValue获得值
                string value1 = Convert.ToString(pi.GetValue(obj, null)).Replace("'", "''");// 替换Sql注入符
                string properName = name.ToString().ToLower();
                bool isVaild = true;
                if (objArray.Length > 0)//如果字段上面有类型的话
                {
                    if ((objArray[0] as System.ComponentModel.DataAnnotations.Schema.NotMappedAttribute) == null)//判断不存在数据库的字段
                    {
                        if ((objArray[0] as Property).Value == "Key")
                        {
                            tableKey = pi.Name;
                            keyValue = value1;
                        }
                    }
                    else
                    {
                        isVaild = false;
                    }
                }

                if (properName != tableKey.ToLower() && properName != BaseSet.PrimaryKey.ToLower() && properName != BaseSet.TableName.ToLower() && value1 != BaseSet.DateTimeLongNull && value1 != BaseSet.DateTimeShortNull && isVaild)
                {
                    if (value1 == BaseSet.NULL)
                    {
                        value1 = "";
                    }
                    string _set = string.IsNullOrWhiteSpace(value1) ? "NULL" : "'" + value1 + "'";
                    _SubSQL.Add("`" + Convert.ToString(name) + "`" + "=" + _set);
                }
            }
            strSQL.Append(string.Join(",", _SubSQL));// 拼接上更新子句
            string condition = " where " + tableKey + "='" + keyValue.Replace("'", "''") + "'";
            strSQL.Append(condition);  // 加入更新条件
            return strSQL;
        }

        private string GetWhereString<T>(Expression<Func<T, bool>> expression) where T : class,new()
        {
            if (expression.Body is BinaryExpression)
            {
                BinaryExpression be = ((BinaryExpression)expression.Body);
                var _left = be.Left;
                var _right = be.Right;
                var _type = be.NodeType;
                return DealLeftAndRight(_left) + GetTypeString(_type.ToString()) + DealLeftAndRight(be.Right);
            }
            return null;
        }

        private string DealLeftAndRight(Expression _left)
        {
            if (_left is BinaryExpression)
            {
                BinaryExpression be = (BinaryExpression)_left;
                var _type = be.NodeType.ToString();
                return DealLeftAndRight(be.Left) + GetTypeString(_type) + DealLeftAndRight(be.Right);
            }
            else
            {
                if (_left is MemberExpression)
                {
                    MemberExpression be = (MemberExpression)_left;
                    return be.Member.Name;
                }
                else
                {
                    var _result = Expression.Lambda(_left).Compile().DynamicInvoke();

                    if (_result is string)
                    {
                        return '"' + _result.ToString() + '"';
                    }
                    if (_result is int)
                    {
                        return _result.ToString();
                    }
                    return string.Empty;
                }
            }
        }

        private string GetTypeString(string _type)
        {
            switch (_type)
            {
                case "Equal":
                    return " = ";
                case "AndAlso":
                    return " AND ";
                case "OrElse":
                    return " OR ";
                default:
                    return string.Empty;
            }
        }

        private string GetTableName<T>(T t) where T : class,new()
        {
            if (t == null) { throw new ArgumentNullException("T"); }
            var __object = t.GetType();
            if (__object != null)
            {
                return __object.Name;
            }
            return null;
        }

        #endregion



        #region Dy

        /// <summary>
        /// 执行一条计算查询结果语句，返回查询结果（object）。
        /// </summary>
        /// <param name="SQLString">计算查询结果语句</param>
        /// <returns>查询结果（object）</returns>
        public object GetSingle(string SQLString)
        {
            return _base.GetSingle(SQLString);
        }
        /// <summary>
        /// 执行一条计算查询结果语句 返回数据表
        /// </summary>
        /// <param name="SQLString"></param>
        /// <returns></returns>
        public DataTable GetDataTable(string SQLString)
        {
            return _base.GetDataTable(SQLString);
        }

        /// <summary>
        /// 执行一条计算查询结果语句  返回winform生码数据表
        /// </summary>
        /// <param name="SQLString">sql</param>
        /// <param name="connstr">连接数据库字符串</param>
        /// <returns></returns>
        public DataTable GetTable(string SQLString,string connstr)
        {
            return _base.GetDataTableDigitcode(SQLString,connstr);
        }

        public DataTable GetTable(string _filed, string _tbname, string _connectionString)
        {
            return _base.GetDataTableDigitcode(_filed, _tbname, _connectionString);
        }

        /// <summary>
        /// 检测数据是否存在
        /// </summary>
        /// <param name="strSql">查询语句</param>
        /// <returns>>0返回true</returns>
		public bool dataExists(string strSql) {
           
			object obj = GetSingle(strSql);
			if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) {
                return false;
			}
			else {
                return true;
			} 
		}
 
        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>
        public void ExecuteSqlTran(Hashtable SQLStringList)
        {
            _base.ExecuteSqlTran(SQLStringList);
        }



        /// <summary>
        /// 执行sql
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql">sql</param>
        /// <param name="connstr">连接数据库字符串</param>
        /// <returns></returns>
        public T ExecuteSql<T>(string sql, string connstr, MySqlParameter[] parameter = null)
        {
            object result = null;
            _base.CommandExecuteNonQuery(sql, parameter, count =>
            {
                result = count;
            }, connstr);

            return result == null ? default(T) : (T)result;
        }

        /// <summary>
        /// 执行insert 返回id
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pars"></param>
        /// <returns></returns>
        public int ExecuteSqlRturnID(string sql, MySqlParameter[] pars)
        {
            return Convert.ToInt32(_base.insertRturnIDSql(sql, pars));
        }
        #endregion


    }
}
